# -*- coding: utf-8 -*-
import sqlite3
import xlrd
from datetime import datetime, timedelta


# 去除list中重复的值
def f9(seq):
    # Not order preserving
    return {}.fromkeys(seq).keys()


def getcusmov(querydatelist):
    head = ('vessel', 'voyage', 'boxno', 'pier', 'boxsize', 'movtype',
            'h986', 'destplace', 'instructtime', 'movflag', 'dealtime', 'feedback')
    url = 'http://www.npedi.com/edi/cusmovAction.do?ctnNo=&s_VelInfo=&companyCode=&selectAll=on&beginOrderSendDate=%s&endOrderSendDate=%s' % (
        querydatelist[-1].replace('-', ''), querydatelist[0].replace('-', ''))
    # 不使用gzip压缩的话有200k左右，但是nbedi不支持gzip
    # content=urllib2.urlopen(url).read()

    encoding_support = ContentEncodingProcessor
    opener = urllib2.build_opener(encoding_support, urllib2.HTTPHandler)
    # 直接用opener打开网页，如果服务器支持gzip/defalte则自动解压缩
    content = opener.open(url).read()
    trs = re.findall(
        '<td height="24" nowrap="nowrap" class="dblue12">.*?</td>', cleanhtml(content))
    rs = []
    for i in range(len(trs) / 13):
        r = {}
        for idx, val in enumerate(trs[i * 13 + 1:i * 13 + 13]):
            r[head[idx]] = re.sub('<.+?>', '', val).strip().decode('gbk')
        rs.append(cleanRow(r))
    return rs


def cleanRow(row):
    for k in row.keys():
        if row[k] == '':
            row[k] = None
    return row


def cleanCell(cell):
    if type(cell) == str:
        cell = cell.strip()
        if cell == '':
            cell = None
    return cell


def readSheet(sheet):
    sh = wb.sheet_by_name(sheet)
    nrows = sh.nrows
    ncols = sh.ncols
    headers = dict((i, sh.cell_value(1, i)) for i in range(ncols))
    return [dict((headers[j], cleanCell(sh.cell_value(i, j))) for j in headers) for i in range(2, nrows)]

# ##########################################
# 主程序开始
# ##########################################

xls = r'allvsl.xls'
wb = xlrd.open_workbook(xls)


conn = sqlite3.connect('npedi.db3')
conn.text_factory = str
conn.row_factory = sqlite3.Row
rs = readSheet('allvsl')
for r in rs:
    conn.execute("replace into allvsl(id,vessel_edicode,vesselname_en,vesselname_ch,country_2code,vesseltype_3code,owner_3code,piercode) values(?,?,?,?,?,?,?,?)", (r[
                 u'船舶ID'], r[u'船舶EDI代码'], r[u'英文船名'], r[u'中文船名'], r[u'国籍'], r[u'船舶类型'], r[u'经营人'], r[u'码头代码']))
conn.commit()
conn.close()
